using System;
using System.Collections.Generic;
using System.Text;
using BusinessEntity;
using System.Data.Common;
using System.Data;


namespace BusinessLogic
{
    public class crConsulta: CrBase //Clase que se encarga de traer las Consultas de las paginas de los Detalles de las Graficas
    {


        #region CombosFormulario 

                    public DataSet GetCarreras()
                    {

                        DataSet ds = new DataSet();
                        try
                        {

                            string sql = "SELECT [TB_SOC_ESI_Carrera_ID] AS ID,[descripcion] AS Carrera, FROM [dbo].[TB_SOC_ESI_Carrera]   union (select '0' as ID,'-SELECCIONE-' as Carrera) ORDER BY ID";
                            ds = objDBSQLClub.ExecuteDataSet(CommandType.Text, sql);
                        }
                        catch
                        {
                            //grabarlo en ocurrencias
                            ds.Tables.Add();
                        }
                        return ds;
                    }
                    public DataSet GetSLOS(string Codigo_dto)
                    {

                        DataSet ds = new DataSet();
                        try
                        {

                            string sql = "SELECT CCOSTO,CCOSTO +' - '+ INICIALES as Iniciales FROM PWCCOMUN..TABCCOSTO wHERE CODIGO_DTO= '" + Codigo_dto + "' AND ESTADO='A' union select '-SELECCIONE-' as CCOSTO,'-SELECCIONE-' as INICIALES   ORDER BY 2 ";
                            ds = objDBSQLClub.ExecuteDataSet(CommandType.Text, sql);
                        }
                        catch
                        {
                            //grabarlo en ocurrencias
                            ds.Tables.Add();
                        }
                        return ds;
                    }
                    public DataSet GetSSOC(string Codigo_dto)
                    {

                        DataSet ds = new DataSet();
                        try
                        {

                            string sql = "SELECT INICIALES,dbo.NombreAbreviado(EMAIL) as NOMBRE FROM PWPERSON Where (((cast([CODIGO_CAT] as int)) Between 11 And 17) AND ((FLAG_JOU_MES)='S')) and codigo_dto='" + Codigo_dto + "'  union select '-SELECCIONE-' as INICIALES,'-SELECCIONE-' as NOMBRE  union SELECT   'SDSO' AS Valor,  'Sin Definir Socio' AS Dato ORDER BY 2";
                            ds = objDBSQLClub.ExecuteDataSet(CommandType.Text, sql);
                        }
                        catch
                        {
                            //grabarlo en ocurrencias
                            ds.Tables.Add();
                        }
                        return ds;
                    }
                    public DataSet GetSGTE(string Codigo_dto)
                    {

                        DataSet ds = new DataSet();
                        try
                        {

                            string sql = "SELECT INICIALES,dbo.NombreAbreviado(EMAIL) as NOMBRE FROM PWPERSON Where (CAST(CODIGO_CAT AS INT)>=15 and CAST(CODIGO_CAT AS INT)<30) and (flag_jou_mes = 'S') and codigo_dto='" + Codigo_dto + "' union select '-SELECCIONE-' as INICIALES,'-SELECCIONE-' as NOMBRE  union SELECT   'SDGE' AS Valor,  'Sin Definir Gerente' AS Dato ORDER BY 2";
                            ds = objDBSQLClub.ExecuteDataSet(CommandType.Text, sql);
                        }
                        catch
                        {
                            //grabarlo en ocurrencias
                            ds.Tables.Add();
                        }
                        return ds;
                    }
                    public DataSet GetIND(string Codigo_cli)
                    {

                        DataSet ds = new DataSet();
                        try
                        {

                            string sql = "select c.cod_seg+'-'+c.cod_sub+'-'+c.cod_ind,i.descripcion from industria I";
                            sql += "  inner join clientes C on i.cod_seg=c.cod_seg and i.cod_sub=c.cod_sub and i.cod_ind=c.cod_ind where c.cod_seg is not null and ";
                            sql += "  c.cod_sub is not null and c.cod_ind is not null and c.codigo_cli='" + Codigo_cli + "'";
                            ds = objDBSQLClub.ExecuteDataSet(CommandType.Text, sql);
                        }
                        catch
                        {
                            //grabarlo en ocurrencias
                            ds.Tables.Add();
                        }
                        return ds;
                    }
        public DataSet GetCRPoCRM(string Codigo_cli)
        {

            DataSet ds = new DataSet();
            try
            {

                string sql = "select case when ((Socio_RESP='' or Socio_RESP is null) OR (GTE_RESP='' or GTE_RESP is null)) then '' else  codigo_cli end from clientes where codigo_cli='" + Codigo_cli + "'";
                ds = objDBSQLClub.ExecuteDataSet(CommandType.Text, sql);
            }
            catch
            {
                //grabarlo en ocurrencias
                ds.Tables.Add();
            }
            return ds;
        }
        #endregion
    
    
    }
}
